package org.huel.backend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;
import org.huel.backend.domain.dto.AttendanceSearchDTO;
import org.huel.backend.domain.dto.AttendanceStatsDTO;
import org.huel.backend.domain.dto.StudentAttendanceDTO;
import org.huel.backend.domain.dto.TopStudentDTO;
import org.huel.backend.domain.entity.Attendance;
import org.huel.backend.domain.entity.AttendanceRecord;
import org.huel.backend.domain.vo.CourseStatVO;
import org.huel.backend.domain.vo.OverallStatVO;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;


@Mapper
public interface AttendanceMapper  {

    /**
     * 基础搜索出勤记录（不带条件）
     */
    @Select("SELECT * FROM attendance ORDER BY timestamp DESC LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "studentName", column = "student_name"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "isAbsent", column = "is_absent")
    })
    List<Attendance> searchAttendanceBasic(@Param("offset") Integer offset, @Param("pageSize") Integer pageSize);

    /**
     * 按班级搜索
     */
    @Select("SELECT * FROM attendance WHERE class_id = #{classId} ORDER BY timestamp DESC LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "studentName", column = "student_name"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "isAbsent", column = "is_absent")
    })
    List<Attendance> searchAttendanceByClass(@Param("classId") String classId, @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);

    /**
     * 按课程搜索
     */
    @Select("SELECT * FROM attendance WHERE course_name = #{courseName} ORDER BY timestamp DESC LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "studentName", column = "student_name"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "isAbsent", column = "is_absent")
    })
    List<Attendance> searchAttendanceByCourse(@Param("courseName") String courseName, @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);

    /**
     * 按学生姓名搜索
     */
    @Select("SELECT * FROM attendance WHERE student_name LIKE CONCAT('%', #{studentName}, '%') ORDER BY timestamp DESC LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "studentName", column = "student_name"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "isAbsent", column = "is_absent")
    })
    List<Attendance> searchAttendanceByStudentName(@Param("studentName") String studentName, @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);

    /**
     * 按出勤状态搜索
     */
    @Select("SELECT * FROM attendance WHERE is_absent = #{isAbsent} ORDER BY timestamp DESC LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(property = "classId", column = "class_id"),
            @Result(property = "studentName", column = "student_name"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "isAbsent", column = "is_absent")
    })
    List<Attendance> searchAttendanceByStatus(@Param("isAbsent") Integer isAbsent, @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);

    /**
     * 统计总记录数
     */
    @Select("SELECT COUNT(*) FROM attendance")
    Long countAttendanceBasic();

    /**
     * 按条件统计记录数
     */
    @Select("SELECT COUNT(*) FROM attendance WHERE class_id = #{classId}")
    Long countAttendanceByClass(@Param("classId") String classId);

    @Select("SELECT COUNT(*) FROM attendance WHERE course_name = #{courseName}")
    Long countAttendanceByCourse(@Param("courseName") String courseName);

    @Select("SELECT COUNT(*) FROM attendance WHERE student_name LIKE CONCAT('%', #{studentName}, '%')")
    Long countAttendanceByStudentName(@Param("studentName") String studentName);

    @Select("SELECT COUNT(*) FROM attendance WHERE is_absent = #{isAbsent}")
    Long countAttendanceByStatus(@Param("isAbsent") Integer isAbsent);

    /**
     * 获取所有班级ID
     */
    @Select("SELECT DISTINCT class_id FROM attendance ORDER BY class_id")
    List<String> getAllClassIds();

    /**
     * 获取所有课程名称
     */
    @Select("SELECT DISTINCT course_name FROM attendance ORDER BY course_name")
    List<String> getAllCourseNames();

    /**
     * 获取所有学生姓名
     */
    @Select("SELECT DISTINCT student_name FROM attendance ORDER BY student_name")
    List<String> getAllStudentNames();

    /**
     * 获取学生出勤统计
     */
    @Select("SELECT student_name as name, student_id as id, " +
            "COUNT(*) as totalCount, " +
            "SUM(CASE WHEN is_absent = 0 THEN 1 ELSE 0 END) as presentCount, " +
            "SUM(CASE WHEN is_absent = 1 THEN 1 ELSE 0 END) as absentCount " +
            "FROM attendance GROUP BY student_id, student_name ORDER BY totalCount DESC")
    @Results({
            @Result(property = "totalCount", column = "totalCount"),
            @Result(property = "presentCount", column = "presentCount"),
            @Result(property = "absentCount", column = "absentCount")
    })
    List<AttendanceStatsDTO> getStudentAttendanceStats();

    /**
     * 获取课程出勤统计
     */
    @Select("SELECT course_name as name, course_name as id, " +
            "COUNT(*) as totalCount, " +
            "SUM(CASE WHEN is_absent = 0 THEN 1 ELSE 0 END) as presentCount, " +
            "SUM(CASE WHEN is_absent = 1 THEN 1 ELSE 0 END) as absentCount " +
            "FROM attendance GROUP BY course_name ORDER BY totalCount DESC")
    @Results({
            @Result(property = "totalCount", column = "totalCount"),
            @Result(property = "presentCount", column = "presentCount"),
            @Result(property = "absentCount", column = "absentCount")
    })
    List<AttendanceStatsDTO> getCourseAttendanceStats();

    /**
     * 获取班级出勤统计
     */
    @Select("SELECT class_id as name, class_id as id, " +
            "COUNT(*) as totalCount, " +
            "SUM(CASE WHEN is_absent = 0 THEN 1 ELSE 0 END) as presentCount, " +
            "SUM(CASE WHEN is_absent = 1 THEN 1 ELSE 0 END) as absentCount " +
            "FROM attendance GROUP BY class_id ORDER BY totalCount DESC")
    @Results({
            @Result(property = "totalCount", column = "totalCount"),
            @Result(property = "presentCount", column = "presentCount"),
            @Result(property = "absentCount", column = "absentCount")
    })
    List<AttendanceStatsDTO> getClassAttendanceStats();
}